﻿CREATE PROCEDURE [dbo].[oisp_Security_WindowsValidation]
	@WindowsUserSID NVARCHAR(100),
	@WindowsLoginName NVARCHAR(256),
	@WindowsGroupSIDList NVARCHAR(MAX),
	@LoginID INT = NULL OUTPUT
AS
/*
Possible Return Values
	0 = User succesfully validated
	-1 = User/Role does not have User-CreateSelf permission
	1 = Login/Contact must be created
	2 = Login Denied
	-99 = Error Occured
	 
*/
	SET NOCOUNT ON
	--Create Temptable
	CREATE TABLE #WinGroups(RowID INT NOT NULL, GroupSID NVARCHAR(256) NOT NULL)
	DECLARE @ReturnVal INT 
	SET @ReturnVal = 0
	BEGIN TRY
		--First Parse out the WindowsGroups list
		INSERT INTO #WinGroups(RowID, GroupSID)
		SELECT RowID, RowValue 
		FROM dbo.tf_system_SplitString(@WindowsGroupSIDList, N',', 0)

		DECLARE @DeniedAccess BIT
		--Check if the user has access

		SELECT  @DeniedAccess = SWU_DeniedAccess 
			, @LoginID = SUS_LoginID 
		FROM dbo.SecurityWindowsUser 
		LEFT OUTER JOIN dbo.SecurityUser 
		 ON SUS_WindowsSID = SWU_SecurityWindowsUserID 
		WHERE SWU_WindowsUserSID = @WindowsUserSID 

		--If we aren't Denied Access Continue
		IF ISNULL(@DeniedAccess, 0) = 1
		BEGIN
			--User does not have access to the system
			RETURN 2
		END 

		--Validate that no Roles are Denied Access
		SELECT @DeniedAccess = MAX(SMG_DeniedAccess)
		FROM dbo.SecurityWindowsGroup 
		INNER JOIN #WinGroups 
		 ON GroupSID = SWG_WindowsGroupSID 

		--Defaule @DeniedAccess to 1 if nothing found here...
		IF ISNULL(@DeniedAccess, 1) = 1
		BEGIN
			--User does not have access to the system
			RETURN 2
		END 

		IF @LogInID IS NULL
		BEGIN 
			--Create a SecurityUser row for the user
			INSERT INTO SecurityUser(SUS_WindowsUser
				, SUS_WindowsSID			
				, SUS_LoginID 
				, SUS_CreatedDate 
				, SUS_LastLogin 
				, SUS_LastActivity
				, SUS_ContactID )
			VALUES(1
				, @WindowsUserSID 
				, @WindowsLoginName
				, GETUTCDATE()
				, GETUTCDATE()
				, GETUTCDATE()
				, NULL)
				
			SELECT @LoginID = SCOPE_IDENTITY()
			--Create the Details Row
			INSERT INTO dbo.SecurityUserDetails(SUD_SecurityUserID 
				, SUD_FailedLoginAttempts 
				, SUD_LastBadPasswordAttempt 
				, SUD_LastPasswordChange 
				, SUD_LockedOut 
				, SUD_LoginEnabled 
				, SUD_PasswordHash )
			VALUES(@LoginID --SUD_SecurityUserID
				, 0 --SUD_FailedLoginAttempts
				, NULL --SUD_LastBadPasswordAttempts
				, NULL --SUD_LastPasswordChange
				, 0 --SUD_LockedOut
				, 1 --SUD_LoginEnabled
				, NULL--SUD_PassowrdHash
				)

			--Now check if the user has CreateSelf Access else Throw error that they Can't login yet
			IF EXISTS (SELECT 1 FROM dbo.oitf_Security_Windows_AccessList(@WindowsUserSID, @WindowsGroupSIDList)
						WHERE SecurityAccessTitle = 'User-CreateSelf')
				RETURN 1
			ELSE
				RETURN -1
		END
		ELSE
			RETURN 0
	END TRY
	BEGIN CATCH
		SET @ReturnVal = -99
		DECLARE 
		@ErrorMessage    NVARCHAR(4000),
		@ErrorNumber     INT,
		@ErrorSeverity   INT,
		@ErrorState      INT,
		@ErrorLine       INT,
		@ErrorProcedure  NVARCHAR(200);

		-- Assign variables to error-handling functions that 
		-- capture information for RAISERROR.
		SELECT 
			@ErrorNumber = ERROR_NUMBER(),
			@ErrorSeverity = ERROR_SEVERITY(),
			@ErrorState = ERROR_STATE(),
			@ErrorLine = ERROR_LINE(),
			@ErrorProcedure = ISNULL(ERROR_PROCEDURE(), '-');

		-- Build the message string that will contain original
		-- error information.
		SELECT @ErrorMessage = 
			N'Error %d, Level %d, State %d, Procedure %s, Line %d, ' + 
				'Message: '+ ERROR_MESSAGE();

		-- Raise an error: msg_str parameter of RAISERROR will contain
		-- the original error information.
		RAISERROR 
			(
			@ErrorMessage, 
			@ErrorSeverity, 
			1,               
			@ErrorNumber,    -- parameter: original error number.
			@ErrorSeverity,  -- parameter: original error severity.
			@ErrorState,     -- parameter: original error state.
			@ErrorProcedure, -- parameter: original error procedure name.
			@ErrorLine       -- parameter: original error line number.
			);

			RETURN -99
	END CATCH

